#region imported Namespaces
using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Text.RegularExpressions;

using BO = PHSRAG.Insight21.BusinessObjects;
using Utility = PHSRAG.Utility;
using ICachePolicy = PHSRAG.Insight21.Policy.ICachePolicy;
using GenerateKey = PHSRAG.Insight21.BusinessObjects.ModuleKey;
#endregion

namespace PHSRAG.Insight21.Admin
{
	/// <summary>
	/// UserDetails contains saveable user contact information in its DataContainer.
	/// </summary>
	public class UserDetails : BO.SaveableBusinessObject
	{
		#region Constants
		private const string EmailTableName = "Email";
		private const string TelephoneTableName = "Telephone";
        private const string OrganizationTableName = "Organization";
		private static string UserDetailsKey = GenerateKey.MakeKey("UserDetails_F940A82C-F599-4863-8243-A1351D9C7E5B",GenerateKey.Admin);
		private const string PrimaryEmailTypeId = "1";
		private const string PrimaryTelephoneTypeId = "1";
		private const string SecondaryEmailTypeId = "2";
		private const string SecondaryTelephoneTypeId = "2";
		#endregion

		#region Instance Variables
		private SqlCommand	updateTelephoneCommand;
		private SqlCommand	updateEmailCommand;
        private SqlCommand  updateOrganizationCommand;
        private bool        doNotUpdate = true;
        private bool doNotUpdateEmail = true;
        private bool doNotUpdatePhone = true;
		#endregion

		#region Public Properties

		/// <summary>Gets the user details primary email data from the underlying DataContainer.</summary>
		public string PrimaryEmail
		{
			get 
			{
				return GetContactDetail(EmailTableName, "EmailAddress", PrimaryEmailTypeId, "");
			}
		}

		/// <summary>Gets the user details secondary email data from the underlying DataContainer.</summary>
		public string SecondaryEmail
		{
			get 
			{
				return GetContactDetail(EmailTableName, "EmailAddress", SecondaryEmailTypeId, "");
			}
		}

		/// <summary>Gets the user details primary phone number area code data from the underlying DataContainer.</summary>
		public string PrimaryPhoneAreaCode
		{
			get 
			{
				return GetContactDetail(TelephoneTableName, "AreaCode", PrimaryTelephoneTypeId, "");
			}
		}

		/// <summary>Gets the user details primary phone number prefix data from the underlying DataContainer.</summary>
		public string PrimaryPhoneNumberPrefix
		{
			get 
			{
				string[] phoneNumber = GetContactDetail(TelephoneTableName, "Number", PrimaryTelephoneTypeId, "-").Split('-');
				return phoneNumber[0];
			}
		}

		/// <summary>Gets the user details primary phone number suffix data from the underlying DataContainer.</summary>
		public string PrimaryPhoneNumberSuffix
		{
			get 
			{
				string phoneNumber = GetContactDetail(TelephoneTableName, "Number", PrimaryTelephoneTypeId, "-");
				string suffix = (new Regex("[^-]+-(?<Suffix>.+)")).Match(phoneNumber).Groups["Suffix"].Value;
				return suffix;
			}
		}

		/// <summary>Gets the user details secondary phone number area code data from the underlying DataContainer.</summary>
		public string SecondaryPhoneAreaCode
		{
			get 
			{
				return GetContactDetail(TelephoneTableName, "AreaCode", SecondaryTelephoneTypeId, "");
			}
		}

		/// <summary>Gets the user details Secondary phone number prefix data from the underlying DataContainer.</summary>
		public string SecondaryPhoneNumberPrefix
		{
			get 
			{
				string[] phoneNumber = GetContactDetail(TelephoneTableName, "Number", SecondaryTelephoneTypeId, "-").Split('-');
				return phoneNumber[0];
			}
		}

		/// <summary>Gets the user details primary phone number suffix data from the underlying DataContainer.</summary>
		public string SecondaryPhoneNumberSuffix
		{
			get 
			{
				string phoneNumber = GetContactDetail(TelephoneTableName, "Number", SecondaryTelephoneTypeId, "-");
				string suffix = (new Regex("[^-]+-(?<Suffix>.+)")).Match(phoneNumber).Groups["Suffix"].Value;
				return suffix;
			}
		}

        /// <summary>Gets the user details organization id from the underlying DataContainer.</summary>
        public int OrganizationId
        {
            get
            {
                return GetContactDetail(OrganizationTableName, "PrimaryOrganizationId", 1);
            }

            set
            {
                if (OrganizationId != value)
                {
                    DataContainer.Tables[OrganizationTableName].Rows[0]["PrimaryOrganizationId"] = value;
                }
            }
        }

        /// <summary>Gets/Sets the doNotUpdate flag for use with organization id.</summary>
        public bool DoNotUpdate
        {
            get
            {
                return doNotUpdate;
            }

            set
            {
                doNotUpdate = value;
            }
        }


        /// <summary>Gets/Sets the doNotUpdateEmail flag for use with Email.</summary>
        public bool DoNotUpdateEmail
        {
            get
            {
                return doNotUpdateEmail;
            }

            set
            {
                doNotUpdateEmail = value;
            }
        }

        /// <summary>Gets/Sets the doNotUpdatePhone flag for use with Phone & feeds.</summary>
        public bool DoNotUpdatePhone
        {
            get { return doNotUpdatePhone; }
            set { doNotUpdatePhone = value; }
        }
		#endregion

		#region Constructor
		/// <summary>
		/// Initialize the state of this object and creates commands to handle updating email, telephone, and organization data.
		/// </summary>
		public UserDetails(DataSet dataSet, ICachePolicy cachePolicy) : base(cachePolicy)
		{
			CreateCommands();
			DataContainer = dataSet;
		}
		#endregion

		#region Base Methods
		/// <summary>Caches this object based on its caching policy.</summary>
		public override void Cache()
		{
			CachePolicy.Cache(UserDetailsKey, this);
		}

		/// <summary>UnCaches this object based on its caching policy.</summary>
		public override void UnCache()
		{
			CachePolicy.UnCache(UserDetailsKey);
		}

		/// <summary>
		/// Expects a valid person id to be used to load this user detail object's
		/// DataContainer by calling the GetUserEmailContactDetails and GetUserTelephoneContactDetails
		/// stored procedures in the database.
		/// If a person id is not specified an Exception is thrown. 
		/// </summary>
		/// <param name="connectionString">Valid database connection string.</param>
		/// <param name="args">The person id to be used to load the user details object.</param>
		public override void Load( string connectionString, params object[] args )
		{
			if ( args.Length != 1 )
			{
				throw new Exception ("User Details Load expects Person ID." );
			}

			int personId = (int)args[0];

			try
			{
				Utility.DataAccess.LoadDataSet(DataContainer, EmailTableName, connectionString,
					"GetUserEmailContactDetails", Utility.DataAccess.BuildSqlParameterArray("@personId", SqlDbType.Int, personId));

				Utility.DataAccess.LoadDataSet(DataContainer, TelephoneTableName, connectionString,
					"GetUserTelephoneContactDetails", Utility.DataAccess.BuildSqlParameterArray("@personId", SqlDbType.Int, personId));

                Utility.DataAccess.LoadDataSet(DataContainer, OrganizationTableName, connectionString,
                    "GetUserOrganizationDetails", Utility.DataAccess.BuildSqlParameterArray("@personId", SqlDbType.Int, personId));
			}
			catch( Exception e)
			{
				Utility.ExceptionReport.WriteToEventLog ("User Details", e, true, Utility.ExceptionReport.ReportType.Error);
			}
		}

		/// <summary>
		/// Persists UserDetails state to the database.
		/// </summary>
		/// <param name="connectionString">Specifies database connection information.</param>
		public override void Save(string connectionString, params object[] args)
		{
			SqlConnection connection = new SqlConnection(connectionString);
			try
			{
				connection.Open();
				ExecuteEmailUpdate(connection);
				ExecuteTelephoneUpdate(connection);
                ExecuteOrganizationUpdate(connection);
			}
			finally
			{
				if (connection.State != ConnectionState.Closed)
					connection.Close();
			}
		}


		#endregion

		#region Private Methods
		/// <summary>
		/// GetContactDetail builds a query against this object's data container in the table specified by
		/// the tableName parameter.  The columnName indicates the requested field and the typeId indicates
		/// the contact detail type filter criteria.  If the data matching the critera is not found, the 
		/// default value that was passed in is returned.
		/// </summary>
		/// <param name="tableName">The name of the Email or Telephone table in this object's DataSet member.</param>
		/// <param name="columnName">The column name in the table specified by tableName parameter that contains 
		/// the data you would like returned.</param>
		/// <param name="typeId">The contact information's type id (ie. "1" for Primary, "2" for Secondary). </param>
		/// <param name="defaultValue">If data is not found matching the parameters passed in the default is returned.</param>
		private string GetContactDetail(string tableName, string columnName, string typeId, string defaultValue)
		{
			// Use the Select method to find all rows matching the filter.
			DataRow[] rows = 
				DataContainer.Tables[tableName].Select(String.Format("TypeId = {0}", typeId));
				
			object o = null;
			if (rows.Length > 0)
			{
				o = rows[0][columnName];
			}
			return (o == null || o == DBNull.Value) ? defaultValue : (string)o;

		}

        /// <summary>
        /// GetContactDetail builds a query against this object's data container in the table specified by
        /// the tableName parameter, in the requested field indicated by the columnName. If the data matching
        /// the critera is not found, the default value that was passed in is returned.
        /// </summary>
        /// <param name="tableName">The name of the table in this object's DataSet member.</param>
        /// <param name="columnName">The column name in the table specified by tableName parameter that contains 
        /// the data you would like returned.</param>
        /// <param name="defaultValue">If data is not found matching the parameters passed in the default is returned.</param>
        private int GetContactDetail(string tableName, string columnName, int defaultValue)
        {
            object o = null;
            if (DataContainer.Tables[tableName].Rows.Count > 0)
            {
                o = DataContainer.Tables[tableName].Rows[0][columnName];
            }
            return (o == null || o == DBNull.Value) ? defaultValue : (int)o;
        }

		/// <summary>
		/// Create the set of SqlCommand objects used for the insertion, deletion, and updates made on
		/// the associated DataTable. Notice that each command is initialized to invoke a stored
		/// procedure implying that each command may have its own set of parameters in line with that
		/// stored procedure.
		/// </summary>
		private void CreateCommands()
		{
			updateEmailCommand = new SqlCommand();
			updateEmailCommand.CommandText = "UpdateEmailAddress";
			updateEmailCommand.CommandType = CommandType.StoredProcedure;
			updateEmailCommand.CommandTimeout = Convert.ToInt32(Utility.Settings.GetKeyValue("Database.Insight.CommandTimeout", "0"));
			updateEmailCommand.Parameters.Add("@emailAddress", SqlDbType.VarChar).SourceColumn = "EmailAddress";
			updateEmailCommand.Parameters.Add("@personId", SqlDbType.Int).SourceColumn = "PersonId";
			updateEmailCommand.Parameters.Add("@emailAddressTypeId", SqlDbType.Int).SourceColumn = "TypeId";
            updateEmailCommand.Parameters.Add("@doNotUpdate", SqlDbType.Bit).Value = (DoNotUpdateEmail) ? 1 : 0;


			updateTelephoneCommand = new SqlCommand();
			updateTelephoneCommand.CommandText = "UpdateTelephoneNumber";
			updateTelephoneCommand.CommandType = CommandType.StoredProcedure;
			updateTelephoneCommand.CommandTimeout = Convert.ToInt32(Utility.Settings.GetKeyValue("Database.Insight.CommandTimeout", "0"));
			updateTelephoneCommand.Parameters.Add("@areaCode", SqlDbType.VarChar).SourceColumn = "AreaCode";
			updateTelephoneCommand.Parameters.Add("@number", SqlDbType.VarChar).SourceColumn = "Number";
			updateTelephoneCommand.Parameters.Add("@personId", SqlDbType.Int).SourceColumn = "PersonId";
			updateTelephoneCommand.Parameters.Add("@telephoneTypeId", SqlDbType.Int).SourceColumn = "TypeId";
            updateTelephoneCommand.Parameters.Add("@doNotUpdate", SqlDbType.Bit).Value = (DoNotUpdatePhone) ? 1 : 0;


            updateOrganizationCommand = new SqlCommand();
            updateOrganizationCommand.CommandText = "UpdateOrganization";
            updateOrganizationCommand.CommandType = CommandType.StoredProcedure;
			updateOrganizationCommand.CommandTimeout = Convert.ToInt32(Utility.Settings.GetKeyValue("Database.Insight.CommandTimeout", "0"));
			updateOrganizationCommand.Parameters.Add("@organizationId", SqlDbType.Int).SourceColumn = "PrimaryOrganizationId";
            updateOrganizationCommand.Parameters.Add("@personId", SqlDbType.Int).SourceColumn = "PersonId";
            updateOrganizationCommand.Parameters.Add("@doNotUpdate", SqlDbType.Bit).Value = (DoNotUpdate) ? 1 : 0;
		}

		/// <summary>
		/// This method is invoked to carry out update of one or more rows from
		/// the Email DataTable. Nothing is done if there are no rows marked for update within the
		/// DataTable.
		/// </summary>
		/// <param name="connection">An instance of an open connection to the database</param>
		private void ExecuteEmailUpdate(SqlConnection connection)
		{
				SqlDataAdapter adapter = new SqlDataAdapter();
				updateEmailCommand.Connection = connection;
				adapter.DeleteCommand = updateEmailCommand;
				adapter.InsertCommand = updateEmailCommand;
				adapter.UpdateCommand = updateEmailCommand;
				adapter.Update(DataContainer, EmailTableName);
		}

		/// <summary>
		/// This method is invoked to carry out update of one or more rows from
		/// the Telephone DataTable. Nothing is done if there are no rows marked for update within the
		/// DataTable.
		/// </summary>
		/// <param name="connection">An instance of an open connection to the database</param>
		private void ExecuteTelephoneUpdate(SqlConnection connection)
		{
			SqlDataAdapter adapter = new SqlDataAdapter();
			updateTelephoneCommand.Connection = connection;
			adapter.DeleteCommand = updateTelephoneCommand;
			adapter.InsertCommand = updateTelephoneCommand;
			adapter.UpdateCommand = updateTelephoneCommand;
			adapter.Update(DataContainer, TelephoneTableName);
		}

        /// <summary>
        /// This method is invoked to carry out update of one or more rows from
        /// the Organization DataTable. Nothing is done if there are no rows marked for update within the
        /// DataTable.
        /// </summary>
        /// <param name="connection">An instance of an open connection to the database</param>
        private void ExecuteOrganizationUpdate(SqlConnection connection)
        {
            SqlDataAdapter adapter = new SqlDataAdapter();
            updateOrganizationCommand.Connection = connection;
            adapter.DeleteCommand = updateOrganizationCommand;
            adapter.InsertCommand = updateOrganizationCommand;
            adapter.UpdateCommand = updateOrganizationCommand;
            adapter.Update(DataContainer, OrganizationTableName);
        }
		#endregion
	}
}
